![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Tuning Rollback SegmentsTo properly configure a systems rollback segments, you must create enough rollback segments and they must be of a sufficient size. Number of Rollback Segments The number of rollback segments should be determined by the number of concurrent transactions in the database. Remember, the fewer transactions per rollback segment, the less contention. A good rule of thumb is to create about one rollback segment for every four concurrent transactions. Rollback contention occurs when too many transactions try to use the same rollback segment at the same time and some of them have to wait. You can tell whether you are seeing contention on rollback segments by looking at the dynamic performance table, V$WAITSTAT. V$WAITSTAT contains the following data related to rollback segments:
The system rollback segment is the original rollback segment created when the database was created. This rollback segment is used primarily for special system functions but is sometimes used when no other rollback segment is available. Typically, the system rollback segment is not used and you do not have to be concerned about it. You can view these values with the following SQL statement: SQL> SELECT class, count 2 FROM V$WAITSTAT 3 WHERE class IN 4 ('undo header', 'undo block', 'system undo header', 'system undo block'); CLASS COUNT ------------------ -------- system undo header 0 system undo block 0 undo header 0 undo block 0 Compare these values with the total number of requests for data. Remember (from earlier in the chapter) that the number of requests for data is equal to the sum of DB BUFFER GETS and CONSISTENT GETS from V$SYSSTAT. Also remember that you can extract that information with the following query: SQL> SELECT SUM(value) "Data Requests" 2 FROM v$sysstat 3 WHERE name IN ('db block gets', 'consistent gets'); Data Requests ------------ 5105 If the number of waits for any of the rollback segment blocks or headers exceeds more than 1 percent of the total number of requests, you should reduce the contention by adding more rollback segments. Size of Rollback Segments Create several different sizes of rollback segments. Each type of rollback segment should be used by the application developer based on the type and length of the transaction:
Size and Number of Extents In general, the best rollback I/O performance can be obtained when there are approximately 10 to 20 extents of equal size per rollback segment. To determine the size and number of extents, use the following formula: Rollback Segment Size = Rsize = Size of largest table / 10 Number of Extents = NE = 10 Size of Extents = Esize = Rsize / NE When creating the rollback segments, use the value of Esize for INITIAL and NEXT; use the value of NE for MINEXTENTS. Even when using these rules, you may not achieve the most effective size for your rollback segments. If dynamic growth is occurring, you may be losing performance.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |